/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package Negocio;

import Datos.Conexion;
import Datos.Global;
import Presentacion.frmAgregarIngrediente;
import Presentacion.frmReceta;
import java.sql.CallableStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.logging.Level;
import java.util.logging.Logger;
import javax.swing.JComboBox;
import javax.swing.JTable;
import javax.swing.table.DefaultTableModel;

/**
 *
 * @author Pedro
 */
public class clsIngrediente {
    
    public static void mostrarTablaIngrediente(JTable jt, String sql) {
        Global.limpiarTabla(jt);
        try {
            ResultSet rs = Conexion.executeQuery(Conexion.conn, sql, null);
            DefaultTableModel dtm = (DefaultTableModel) jt.getModel();
            while (rs.next()) {
                Object[] ingrediente = new Object[6];
                ingrediente[0] = rs.getInt(1);
                ingrediente[1] = rs.getString(2);
                ingrediente[2] = rs.getString(3);
                ingrediente[3] = rs.getFloat(4);
                
                String sql1 = "CALL `obtener_precioCantidad`(" + rs.getInt(1) + ")"; //obtener precio y cantidad del insumo del id  ?
                ResultSet set = Conexion.executeQuery(Conexion.conn, sql1, null);
                while (set.next()) {
                    ingrediente[4] = set.getFloat(1);
                    ingrediente[5] = set.getFloat(2);
                }
                dtm.addRow(ingrediente);
            }
        } catch (SQLException ex) {
            System.out.println("error al cargar tabla ingrediente " + ex);
        }
        
    }
    
    public static void insertar_Ingrediente(int id_insumo, int id_receta, float porcion) {
       
            try {
                String sql = "CALL `insert_Ingrediente`(?,?,?)";
                CallableStatement proc = Conexion.conn.prepareCall(sql);
                proc.setInt(1, id_insumo);
                proc.setInt(2, id_receta);
                proc.setFloat(3, porcion);
                proc.executeUpdate();
            } catch (SQLException ex) {        
                System.out.println("Error al ingresar el ingrediente " + ex);
            }
        
    }
    
    public static void eliminar_ingredienteUnico(int id_insumo, int id_Receta) {
        try {
            String sql = "CALL `deleted_IngredienteU`(?,?)";
            CallableStatement proc = Conexion.conn.prepareCall(sql);
            proc.setInt(1, id_insumo);
            proc.setInt(2, id_Receta);
            proc.executeUpdate();
        } catch (SQLException ex) {
            System.out.println("Error al eliminar el ingrediente " + ex);
        }
    }
    
    public static void cargarListaIngredientes(JComboBox jcb, int idReceta) {
        jcb.removeAllItems();
        try {
            String sql = "CALL `obtener_listaIngrediente`(" + idReceta + "," + 1 + ")";
            ResultSet rs = Conexion.executeQuery(Conexion.conn, sql, null);
            while (rs.next()) {
                jcb.addItem(rs.getString(2));
            }
        } catch (SQLException ex) {
            System.out.println("error al obtener lista de ingrediente " + ex);
        }
    }
    
    public static float getStockInsumo(int idInsumo) {
        try {
            String sql = "CALL `obtener_stockInsumo`(" + idInsumo + ")";
            ResultSet rs = Conexion.executeQuery(Conexion.conn, sql, null);
            while (rs.next()) {
                return rs.getFloat(1);
            }
        } catch (SQLException ex) {
            System.out.println("error al  obtener stock insumo");
        }
        return 0;
    }
    
    public static int getStockMinimo(int idInsumo) {
        try {
            String sql = "CALL `obtener_stockMinimo`(" + idInsumo + ")";
            ResultSet rs = Conexion.executeQuery(Conexion.conn, sql, null);
            while (rs.next()) {
                return rs.getInt(1);
            }
        } catch (SQLException ex) {
            System.out.println("error al  obtener stock minimo");
        }
        return 0;
        
    }
    
    public static float getCostoTotalIngrediente(int idReceta){
        try {
            String sql="CALL `costoTotalIngrediente`("+idReceta+")";
            ResultSet rs=Conexion.executeQuery(Conexion.conn, sql, null);
            float total=0;
            while (rs.next()){
                total +=frmAgregarIngrediente.costoVerdadero(rs.getFloat(2), rs.getFloat(1), rs.getFloat(3));
            }
            return total;
        } catch (SQLException ex) {
            System.out.println("error al sumar ingrediente "+ex);
        }
        return -1;
    }
    
    public static float getValorProteicoIngrediente(int idReceta){
        try {
            String sql="CALL `costoTotalIngrediente`("+idReceta+")";
            ResultSet rs=Conexion.executeQuery(Conexion.conn, sql, null);
            float total=0;
            while (rs.next()){
                total +=frmAgregarIngrediente.costoVerdadero(rs.getFloat(4), 100, rs.getFloat(3));
            }
            return total;
        } catch (SQLException ex) {
            System.out.println("error al sumar ingrediente "+ex);
        }
        return -1;
    }
}
